Helpful Information

Category: GUI

GUI -> Excel -> GUI

I'm working on a little project where I'm looking to create a web-based graphical user interface that takes the user's inputs, connects with Microsoft Excel, uses the inputs to do some calculations, takes the Excel results, and then display outputs on the GUI. I'm not that savvy with programming, so I'm just looking for a general framework as to how I go about doing this (i.e. what languages do I use and how I implement such a thing?). Thanks in advance for any advice!

Hey bryanwt3,

1. Could you please go into more detail regarding what calculations you have. The reason I'm asking is, does Excel necessarily have to be the means by which you perform them? At a first glance, it seems that you're overcomplicating it a little, but then again I'm keeping an open mind, so I'm not necessarily claiming that you shouldn't use Excel.

2. As far as what languages/platforms you need to work with, they can be (but not limited to):

Javascript (for client-side)
CSS (for styling)
Either PHP, Classic ASP, ASP.Net (for server-side)
You might also need to know SQL to do your calculations

Mike

Mike,

I have the same request as well. I have a detailed excel spreadsheet (model) with tabs that have worksheets performing calculations. I centralized one tab that serves as my input schedule - that is, one can input the values in the certain cells on that tab and the calculations will follow through to the other tabs.

The reason I'm using excel is that I'm familiar with it as are the other users of my model.

I would like it to be more user friendly - hence, the requirement or the request for a GUI interface that will allow the users to post the values on the screen that gets linked to the cells on the input page and from there drives the calculations on the other tabs in the spreadsheet.
I understand that VBA programming may be required to do so - not overly familiar with VBA (but I'm pretty good at excel).

Is there an easier approach or can the VBA instructions be easily followed? Or alternatively, is there GUI interface software (3rd party) that can be incorporated into excel that will allow someone to create the GUI interface?

Your help would be greatly appreciated.

Thanks in advance.

Hi PCExcel,

Will this spreadsheet be located only on one machine?

Mike

Mike,

Yes - it will be as I will be the owner of the spreadsheet. However, I will send it out to the various users to allow them to input the values as they wish to perform sensitivity analyses.

Hi PCExcel,

You can certainly use VBA to build up a form, where you will enter values which will then interact with sheets/cells. I think VBA is not difficult to learn, especially if you are familiar with the Excel environment. One of the ways you can learn is by recording a macro and then viewing the resulting code. It's pretty straight forward. You'll begin to understand once you see it.

Having said that, I personally would not use VBA in your situation. I would either build a Web page, or a Windows Application that would intract with a database server that would hold all the data, rather then an excel file. Now, there are a million reasons I have for this, and unfortunately I don't have the time to explain those (my apology).

Don't rule out the VBA approach completly(I'm just one person with an opinion), but if you're considering my approach, then you'll either have to learn the languages I posted above, or you'll have to pay somebody to write the GUI for you.

Regards,
Mike

Mike,

Thanks for the response. In response to your suggestions:

1. Recording a macro - not sure how that process will work in creating a GUI interface.

2. Applications - are there any 3rd party software that has a easy to follow wizard menu that help one create a GUI interface without going through the time consuming exercise of learning a language (whether it is VBA for above or web based application).

Please let me know.

Thanks again.

Hi PCExcel,

I mentioned recording an Excel macro because it is one way of getting familiarized with VBA. As far as how exactly you create the GUI using VBA, IF you decide to go with this route yourself, you will have no choice but to spend the time and to learn how to do it, there is no other way around it. Again, that's not to say that you won't pick it up. I think once you get into it, you will begin to understand, especially with the fact that you know Excel well. Anyway, follow these steps to create a sample GUI that will interact with cells. Basically it's a textbox and a button. When you click on the button, whatever is in the textbox goes in cell "A1" of sheet "Sheet1".

1. Open a fresh Excel workbook. Press ALT-F11 to go into the VBA Project window.
2. Once there, create a new form by right-clicking on VBAProject->Insert->UserForm. A toolbox should come up exposing various controls that you can put on it. If it doesn't come up, you can open it from the menu on top.
3. Drag a textbox and a button onto the form.
4. Double-click on the button, and it should automatically create the click handler "CommandButton1_Click()" for it. Make this event handler look like this:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells(1, 1).Value = TextBox1.Value
End Sub
5. Now insert a new module by right-clicking on VBAProject->Insert->Module. Module1 will be created
6. Inside it, put the following code:

Sub auto_open()
Load UserForm1
UserForm1.Show
End Sub
7. Save your file. When you open it, just enable macro. Your form should now come up.

So, that's that for VBA. It's just a small example to get you started. As far as third party solutions, sorry, I don't know any. However I have one comment about it. If there is a solution like that, chances are that you'll have to pay for it, and if you're willing to spend money, you might as well pay somebody to build a custom solution for you, which might actually be cheaper and better overall.

Regards,
Mike

Mike,

Thank you for the follow-up. I will try your VBA suggestion.

Thanks again.